﻿using System;
using Avanzis.QueryBuilder.Fluent;
using Avanzis.QueryFramework;
using NUnit.Framework;

namespace Avanzis.QueryBuilder.NUnit.Fluent
{
    [TestFixture]
    public class SelectTypedTests : SelectBuilderTest
    {
       
        [SetUp]
        public void SetUp()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2008Dialect;
            QueryProcessor.ResetProcessor();
        }

        [Test]
        public override void SimpleSelectAll()
        {
            Select<Article> sb = new Select<Article>();

            string result = "SELECT *\n" +
                            "FROM [Article]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleSelectFields()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .From<Article>();

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Article]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public void SimpleSelectFields_With_Typed_Select_And_No_Fields()
        {
            Select<Article> sb = new Select<Article>();

            string result = "SELECT *\n" +
                            "FROM [Article]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleDistinct()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Distinct();

            string result = "SELECT DISTINCT ArticleId,Name\n" +
                            "FROM [Article]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public void SimpleSelectFields_With_Pluralization_Enabled()
        {
            QueryFrameworkConfiguration.Conventions.PluralizeTables = true;

            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name);
                

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());

            QueryFrameworkConfiguration.Conventions.PluralizeTables = false;
        }



        [Test]
        public void SimpleSelectFields_With_Custom_Pluralization_Enabled()
        {
            QueryFrameworkConfiguration.Conventions.PluralizeTables = true;

            QueryFrameworkConfiguration.Conventions.TableNamePluralizer = new CustomPluralizer();
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name);


            string result = "SELECT ArticleId,Name\n" +
                            "FROM [prefix_Article]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());

            QueryFrameworkConfiguration.Conventions.PluralizeTables = false;
            QueryFrameworkConfiguration.Conventions.TableNamePluralizer = null;
        }

        public class CustomPluralizer: ITableNamePluralizer
        {
            public string Pluralize(string typeName)
            {
                return "prefix_" + typeName;
            }
        }

        [Test]
        public override void SelectFieldsWithFilter()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Where(a => a.Active, 1);

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Article]\n" +
                            "WHERE (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SelectFieldsWithFilterAndOperator()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Where(a => a.Active, SqlOperator.GreaterOrEqual, 0);

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Article]\n" +
                            "WHERE (Active >= 0)\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleOrderBy()
        {
            Select<Article> sb = new Select<Article>()
                .OrderByDescending(a => a.ArticleId);

            string result = "SELECT *\n" +
                            "FROM [Article]\n" +
                            "ORDER BY ArticleId DESC\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleGroupBy()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field("AVG(UnitCost)", "Coste medio por unidad")
                .GroupBy(a => a.ArticleId);

            string result = "SELECT ArticleId,AVG(UnitCost) AS 'Coste medio por unidad'\n" +
                            "FROM [Article]\n" +
                            "GROUP BY ArticleId\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleHaving()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field("AVG(UnitCost)", "Coste medio por unidad")
                .GroupBy(a => a.ArticleId)
                .Having(a => a.Active, 1);

            string result = "SELECT ArticleId,AVG(UnitCost) AS 'Coste medio por unidad'\n" +
                            "FROM [Article]\n" +
                            "GROUP BY ArticleId\n" +
                            "HAVING (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleJoin()
        {
            Select<Article> sb = new Select<Article>()
                .Join<User>(a => a.UserId, u => u.UserId);

            string sql = sb.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article] " +
                            "INNER JOIN [User] ON ((Article.UserId = User.UserId))\n";

            Assert.AreEqual(result, sql);
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public void Join_With_Typed_Select_And_Fields()
        {
            Select<Article> sb = new Select<Article>()
                .Field<Article>(a => a.ArticleId)
                .Field<Article>(a => a.Name)
                .Field<User>(u => u.UserId)
                .Field<User>(u => u.FullName)
                .Join<User>(a => a.UserId, u => u.UserId);

            string sql = sb.ToString();

            string result = "SELECT Article.ArticleId,Article.Name,User.UserId,User.FullName\n" +
                            "FROM [Article] " +
                            "INNER JOIN [User] ON ((Article.UserId = User.UserId))\n";

            Assert.AreEqual(result, sql);
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public void Join_With_Typed_Select_With_Explicit_Objects()
        {
            Select<Article> sb = new Select<Article>()
                .Join<Article, User>(a => a.UserId, u => u.UserId);

            string sql = sb.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article] " +
                            "INNER JOIN [User] ON ((Article.UserId = User.UserId))\n";

            Assert.AreEqual(result, sql);
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void MultipleJoins()
        {
            Select<Article> sb = new Select<Article>()
                .Join<User>(a => a.UserId, u => u.UserId)
                .LeftJoin<User, Group>(a => a.GroupId, g => g.GroupId);


            string sql = sb.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article] " +
                            "INNER JOIN [User] ON ((Article.UserId = User.UserId)) " +
                            "LEFT JOIN [Group] ON ((User.GroupId = Group.GroupId))\n";

            Assert.AreEqual(result, sql);
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        public override void SingleJoinWithMultipleConditions()
        {
            throw new NotImplementedException();
        }

        public override void ReuseFieldsInWhereAndGroupBy()
        {
            throw new NotImplementedException();
        }

        [Test]
        public override void SimpleLimit10()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Take(10);

            string result = "SELECT TOP 10 ArticleId,Name\n" +
                            "FROM [Article]\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleOffset10()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MySql;
            QueryProcessor.ResetProcessor();

            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Skip(10);

            string result = "SELECT ArticleId,Name\n" +
                            "FROM `Article`\n" +
                            "OFFSET 10\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleLimit20Offset10()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MySql;
            QueryProcessor.ResetProcessor();

            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Skip(10)
                .Take(5);

            string result = "SELECT ArticleId,Name\n" +
                            "FROM `Article`\n" +
                            "LIMIT 5\n" +
                            "OFFSET 10\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

         [Test]
        public override void SimpleLimit20Offset10WithOrderBy()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MySql;
            QueryProcessor.ResetProcessor();

            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Skip(10)
                .Take(5)
                .OrderBy(a => a.Name);
             

            string result = "SELECT ArticleId,Name\n" +
                            "FROM `Article`\n" +
                            "ORDER BY Name ASC\n" +
                            "LIMIT 5\n" +
                            "OFFSET 10\n";

            Assert.AreEqual(result, sb.ToString());
            Select<Article> cloned = (Select<Article>) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public void Where_With_Typed_Property()
        {
            Select<Article> select = new Select<Article>()
                .Where(a => a.ArticleId, 1);

            string sql = select.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article]\n" +
                            "WHERE (ArticleId = 1)\n";

            Assert.AreEqual(result, sql);
        }

        [Test]
        public void Where_With_Typed_Property_And_IsNull_Operator()
        {
            Select<Article> select = new Select<Article>()
                .Where(a => a.UserId, SqlOperator.IsNull);

            string sql = select.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article]\n" +
                            "WHERE (UserId IS NULL)\n";

            Assert.AreEqual(result, sql);
        }


        [Test]
        public void Where_With_Custom_Property_And_Operator()
        {
            Select<Article> select = new Select<Article>()
                .Where("ArticleId", SqlOperator.GreaterOrEqual, 5);

            string sql = select.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article]\n" +
                            "WHERE (ArticleId >= 5)\n";

            Assert.AreEqual(result, sql);
        }

        [Test]
        public void Where_With_Join_And_Typed_Property()
        {
            Select<Article> select = new Select<Article>()
                .Join<User>(a => a.UserId, u => u.UserId)
                .Where<User>(u => u.GroupId, 2);

            string sql = select.ToString();

            string result = "SELECT *\n" +
                            "FROM [Article] " +
                            "INNER JOIN [User] ON ((Article.UserId = User.UserId))\n" +
                            "WHERE (User.GroupId = 2)\n";

            Assert.AreEqual(result, sql);
        }

        [Test]
        public void Field_From_Nested_Property()
        {
            Select<Parent> select = new Select<Parent>()
                .Field(p => p.Id)
                .Field(p => p.Child1.ChildProperty);

            string sql = select.ToString();

            string result = "SELECT Id,Child1" + QueryFrameworkConfiguration.Conventions.SubPropertySeparator +
                            "ChildProperty\n" +
                            "FROM [Parent]\n";

            Assert.AreEqual(result, sql);
        }

        [Test]
        public void Field_From_Nested_Property_Uses_SubPropertySeparator()
        {
            var separator = QueryFrameworkConfiguration.Conventions.SubPropertySeparator;

            QueryFrameworkConfiguration.Conventions.SubPropertySeparator = "!";

            Select<Parent> select = new Select<Parent>()
                .Field(p => p.Id)
                .Field(p => p.Child1.ChildProperty);

            string sql = select.ToString();

            string result = "SELECT Id,Child1!ChildProperty\n" +
                            "FROM [Parent]\n";

            QueryFrameworkConfiguration.Conventions.SubPropertySeparator = separator;

            Assert.AreEqual(result, sql);
        }

        [Test]
        public void Where_WithNested_Property()
        {
            Select<Parent> select = new Select<Parent>()
                .Where<Parent>(p => p.Child1.ChildId, 1);

            string sql = select.ToString();

            string result = "SELECT *\n" +
                            "FROM [Parent]\n" +
                            "WHERE (Parent.Child1_ChildId = 1)\n";

            Assert.AreEqual(result, sql);
        }

        [Test]
        public void Select_With_Parameters_Stores_Parameter()
        {
            Select<Article> sb = new Select<Article>()
                .Field(a => a.ArticleId)
                .Field(a => a.Name)
                .Where(a => a.Active, new QueryParameter<Article>(a => a.Active, 1));

            
        }


        public class Article
        {
            public int ArticleId { get; set; }
            public string Name { get; set; }
            public bool Active { get; set; }
            public int UserId { get; set; }
            public int CategoryId { get; set; }
        }

        public class User
        {
            public int UserId { get; set; }
            public string FullName { get; set; }
            public int GroupId { get; set; }
        }

        public class Group
        {
            public int GroupId { get; set; }
            public string Name { get; set; }
        }

        public class Parent
        {
            public int Id { get; set; }
            public Child Child1 { get; set; }
        }

        public class Child
        {
            public int ChildId;
            public string ChildProperty { get; set; }
        }

        //[Test]
        //public void MapTo_Maps_Simple_Select()
        //{
        //     Select<Article> select = new Select<Article>()
        //        .Field(a => a.Name)
        //        .MapTo<ArticleDTO>( new Dictionary<
        //                   { a => a.

        //})
        //}

        //public class ArticleDTO
        //{
        //    public string ArticleName { get; set; }
        //}
    }
}